package com.timerecord.db;

import com.timerecord.entity.TimeRecord;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class DBHelper {
    private static final String DB_NAME = "time_record.db";
    private static final String TABLE_NAME = "time_records";

    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        String url = "jdbc:sqlite:" + DB_NAME; // 数据库文件存储在项目根目录
        return DriverManager.getConnection(url);
    }

    // 初始化数据库（创建表+触发器）
    public void init() {
        // 创建表结构
        String createTableSql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "category TEXT NOT NULL," +
                "details TEXT," +
                "start_time DATETIME NOT NULL," +
                "end_time DATETIME," +
                "duration_seconds INTEGER," +
                "create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP," +
                "update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP" +
                ")";

        // 创建更新时间触发器（自动更新update_time）
        String createTriggerSql = "CREATE TRIGGER IF NOT EXISTS update_time_trigger " +
                "AFTER UPDATE ON " + TABLE_NAME + " " +
                "FOR EACH ROW " +
                "BEGIN " +
                "UPDATE " + TABLE_NAME + " SET update_time = CURRENT_TIMESTAMP WHERE id = OLD.id; " +
                "END";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute(createTableSql);
            stmt.execute(createTriggerSql);
            log.info("数据库初始化成功，表[" + TABLE_NAME + "]已就绪");
        } catch (SQLException e) {
            log.error("数据库初始化失败", e);
        }
    }

    // 保存记录（新增或更新）
    public void saveRecord(TimeRecord record) {
        String sql;
        if (record.getEndTime() == null) {
            // 新增开始记录
            sql = "INSERT INTO " + TABLE_NAME + "(category, details, start_time) VALUES (?, ?, ?)";
        } else {
            // 更新结束记录
            sql = "UPDATE " + TABLE_NAME + " SET end_time = ?, duration_seconds = ? WHERE id = ?";
        }

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            if (record.getEndTime() == null) {
                pstmt.setString(1, record.getCategory());
                pstmt.setString(2, record.getDetails());
                pstmt.setTimestamp(3, new Timestamp(record.getStartTime().getTime()));
            } else {
                pstmt.setTimestamp(1, new Timestamp(record.getEndTime().getTime()));
                pstmt.setLong(2, record.getDurationSeconds());
                pstmt.setInt(3, record.getId());
            }
            pstmt.executeUpdate();
            log.info("记录保存成功：{} - {}", record.getCategory(), record.getDetails());
        } catch (SQLException e) {
            log.error("记录保存失败", e);
        }
    }

    // 查询所有记录（用于表格显示）
    public List<TimeRecord> queryAllRecords() {
        List<TimeRecord> records = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " ORDER BY start_time DESC limit 0, 50";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                TimeRecord record = new TimeRecord();
                record.setId(rs.getInt("id"));
                record.setCategory(rs.getString("category"));
                record.setDetails(rs.getString("details"));
                record.setStartTime(rs.getTimestamp("start_time"));
                record.setEndTime(rs.getTimestamp("end_time"));
                record.setDurationSeconds(rs.getLong("duration_seconds"));
                record.setCreateTime(rs.getTimestamp("create_time"));
                record.setUpdateTime(rs.getTimestamp("update_time"));
                records.add(record);
            }
        } catch (SQLException e) {
            log.error("查询记录失败", e);
        }
        return records;
    }

    /**
     * 按时间范围查询记录（开始时间 <= startTime <= 结束时间）
     */
    public List<TimeRecord> queryRecordsByTimeRange(java.util.Date start, java.util.Date end) {
        List<TimeRecord> records = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME +
                " WHERE start_time BETWEEN ? AND ? " +
                " ORDER BY start_time DESC";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 转换为包含时间的Timestamp（开始时间设为00:00:00，结束时间设为23:59:59）
            Timestamp startTs = new Timestamp(start.getTime());
            Timestamp endTs = new Timestamp(end.getTime() + 86399999); // 加23小时59分59秒

            pstmt.setTimestamp(1, startTs);
            pstmt.setTimestamp(2, endTs);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    TimeRecord record = new TimeRecord();
                    record.setId(rs.getInt("id"));
                    record.setCategory(rs.getString("category"));
                    record.setDetails(rs.getString("details"));
                    record.setStartTime(rs.getTimestamp("start_time"));
                    record.setEndTime(rs.getTimestamp("end_time"));
                    record.setDurationSeconds(rs.getLong("duration_seconds"));
                    records.add(record);
                }
            }
        } catch (SQLException e) {
            log.error("按时间范围查询失败", e);
        }
        return records;
    }

    // 查询所有已有的类别（去重，用于类别下拉框）
    public List<String> queryAllCategories() {
        List<String> categories = new ArrayList<>();
        String sql = "SELECT DISTINCT category FROM " + TABLE_NAME + " WHERE category IS NOT NULL ORDER BY category";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                categories.add(rs.getString("category"));
            }
        } catch (SQLException e) {
            log.error("查询类别失败", e);
        }
        return categories;
    }

    // 按类别查询已有的明细（去重，用于明细下拉框联动）
    public List<String> queryDetailsByCategory(String category) {
        List<String> detailsList = new ArrayList<>();
        if (category == null || category.trim().isEmpty()) {
            return detailsList; // 类别为空时返回空列表
        }

        String sql = "SELECT DISTINCT details FROM " + TABLE_NAME + 
                     " WHERE category = ? AND details IS NOT NULL AND details != '' " +
                     " ORDER BY details";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, category);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    detailsList.add(rs.getString("details"));
                }
            }
        } catch (SQLException e) {
            log.error("按类别查询明细失败", e);
        }
        return detailsList;
    }

    // 查询最后一条未结束的记录（恢复计时状态）
    public TimeRecord getLastUnfinishedRecord() {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE end_time IS NULL ORDER BY start_time DESC LIMIT 1";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            if (rs.next()) {
                TimeRecord record = new TimeRecord();
                record.setId(rs.getInt("id"));
                record.setCategory(rs.getString("category"));
                record.setDetails(rs.getString("details"));
                record.setStartTime(rs.getTimestamp("start_time"));
                return record;
            }
        } catch (SQLException e) {
            log.error("查询未结束记录失败", e);
        }
        return null;
    }

    // 补充到DBHelper.java中

    /**
     * 更新记录的所有字段（用于编辑后保存）
     */
    public void updateRecord(TimeRecord record) {
        String sql = "UPDATE " + TABLE_NAME + " SET " +
                "category = ?, details = ?, start_time = ?, end_time = ?, duration_seconds = ? " +
                "WHERE id = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, record.getCategory());
            pstmt.setString(2, record.getDetails());
            pstmt.setTimestamp(3, new Timestamp(record.getStartTime().getTime()));
            pstmt.setTimestamp(4, record.getEndTime() != null ? new Timestamp(record.getEndTime().getTime()) : null);
            pstmt.setLong(5, record.getDurationSeconds() != null ? record.getDurationSeconds() : 0);
            pstmt.setInt(6, record.getId());
            pstmt.executeUpdate();
            log.info("记录更新成功：ID={}", record.getId());
        } catch (SQLException e) {
            log.error("记录更新失败", e);
        }
    }

    /**
     * 删除记录
     */
    public void deleteRecord(int id) {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            log.info("记录删除成功：ID={}", id);
        } catch (SQLException e) {
            log.error("记录删除失败", e);
        }
    }
}